CREATE TABLE [dbo].[tb_Alumnos]
(
[idAlumno] [int] NOT NULL IDENTITY(1, 1),
[Documento] [int] NOT NULL CONSTRAINT [DF_tb_Alumnos_Documento] DEFAULT ((0)),
[Tipo] [int] NOT NULL CONSTRAINT [DF_tb_Alumnos_Tipo] DEFAULT ((1)),
[Apellidos] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Nombres] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TipoDomicilio] [int] NULL,
[Calle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Numero] [int] NULL,
[Piso] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Departamento] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RefGeografica] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Seccion] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Parcela] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Localidad] [int] NOT NULL CONSTRAINT [DF_tb_Alumnos_Localidad] DEFAULT ((21)),
[Telefono] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LugarDeNacimiento] [int] NULL,
[CodigoPostal] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Sexo] [int] NOT NULL,
[FechaNacimiento] [datetime] NULL,
[EstadoCivil] [int] NOT NULL CONSTRAINT [DF_tb_Alumnos_EstadoCivil] DEFAULT ((1)),
[Nacionalidad] [int] NULL CONSTRAINT [DF_tb_Alumnos_Nacionalidad] DEFAULT ((1)),
[Seguro] [int] NULL,
[Cohorte] [int] NULL,
[DocRepresentanteBecas] [int] NULL,
[NombreRepresentanteBecas] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cuise] [int] NULL,
[idAnterior] [int] NULL,
[Observaciones] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Escolarizado] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LenguaFamilia] [int] NULL,
[UrbanoRural] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Incapacidad] [int] NULL,
[Provincia] [int] NULL,
[cargo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tb_Alumnos_cargo] DEFAULT (suser_sname()),
[hora] [datetime] NULL,
[AñoLectivo] [int] NULL CONSTRAINT [DF_tb_Alumnos_AñoLectivo] DEFAULT ((2010)),
[Organizacion] [int] NULL,
[Curso] [int] NULL,
[Alumno] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[AlumnosDuplicados] ON [dbo].[tb_Alumnos]
FOR INSERT
AS

SET NOCOUNT ON
DECLARE @documento INT

SELECT  @documento = Documento FROM Inserted 
update tb_alumnos set cargo = system_user, hora = getdate() where idalumno = (select idalumno from inserted)
if (SELECT count( *) FROM sagenet.dbo.tb_Alumnos WHERE @Documento = Documento) >1
 BEGIN
	ROLLBACK transaction 
	RAISERROR('El Alumno  ya ha sido ingresado.', 16,1)
          
END
if @documento = 0 
 BEGIN
	delete from tb_alumnos where idalumno = (select idalumno from inserted)
          
END
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[deletetb_Alumnos] ON [dbo].[tb_Alumnos]
 FOR DELETE AS
print ''
/*
		 DELETE tb_Cursadas FROM tb_Cursadas
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_BecasYPrestamos FROM tb_BecasYPrestamos
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_Inscripciones FROM tb_Inscripciones
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_FichaConducta FROM tb_FichaConducta
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_FichaPsicoPedagogica FROM tb_FichaPsicoPedagogica
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_FichaSanitaria FROM tb_FichaSanitaria
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_FichaSocioEconomica FROM tb_FichaSocioEconomica
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_Familia FROM tb_Familia
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_CarrerasAlumnos FROM tb_CarrerasAlumnos
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_Solicitudes FROM tb_Solicitudes
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_Inasistencias FROM tb_Inasistencias
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_AlumnoDivision FROM tb_AlumnoDivision
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_Examenes FROM tb_Examenes
		 WHERE Descripcion = (SELECT idAlumno FROM deleted)
		 DELETE tb_Pases FROM tb_Pases
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_AsignacionServiciosExtraordinarios FROM tb_AsignacionServiciosExtraordinarios
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_Entrevistas FROM tb_Entrevistas
		 WHERE Alumno = (SELECT idAlumno FROM deleted)
		 DELETE tb_Aportes FROM tb_Aportes
		 WHERE Alumno = (SELECT idAlumno FROM deleted)


*/
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[InscripcionMatriculacion] ON [dbo].[tb_Alumnos] 
FOR INSERT
AS


declare @Alumno int
declare @añolectivo INT 
declare @organizacion int
declare @curso int
declare @seccion int
declare @fecha datetime
declare @inscripciondeorigen int
set @fecha = getdate()



set select @Alumno = alumno,@organizacion = organizacion,@curso = curso,@añolectivo = añolectivo from inserted
-- inserto el alumno existente en la tabla inscripciones

	if @Alumno is not null and @organizacion is not null and @añolectivo is not null and @Curso is  null and not exists (Select * from tb_Inscripciones where Alumno = @Alumno and organizacion = @organizacion and aniolectivo = @añolectivo)
--	if @Alumno is not null and @organizacion is not null and @añolectivo is not null and @Curso is  null   VERSION ANTERIOR
                  begin
		select @alumno = alumno,@añolectivo = añolectivo,@curso = Curso,@organizacion = organizacion from inserted
		INSERT INTO tb_Inscripciones (Organizacion,AnioLectivo,Alumno,Carrera,Curso,Escolarizado,Resuelta)
		VALUES (@organizacion,@AñoLectivo,@alumno,5,@Curso,'N','N')

                  end
	

	if @Alumno is not null and @organizacion is not null and @añolectivo is not null and @curso is not null begin
		select @alumno = alumno,@añolectivo = añolectivo,@curso = curso,@organizacion = organizacion from inserted
		INSERT INTO tb_Inscripciones (Organizacion,AnioLectivo,Alumno,Carrera,Curso,Escolarizado,Resuelta)
		VALUES (@organizacion,@AñoLectivo,@alumno,5,@Curso,'S','S')
		
		
		if not exists (Select * from tb_AlumnoDivision where Alumno = @alumno and AnioLectivo = @AñoLectivo and Division = @seccion) begin
			INSERT INTO tb_alumnodivision(Alumno,Division,AnioLectivo,Desde,Hasta,PaseDeOrigen,InscripcionDeOrigen,Baja,CausaBaja,cargo,hora)
			VALUES (@alumno,@curso,@AñoLectivo,@Fecha,null,null,@InscripcionDeOrigen,'N',null,system_user,getdate())
		end

	end
/*
	if @Alumno is null and @organizacion is not null and @añolectivo is not null and @curso is not null begin
		select @alumno = idalumno,@añolectivo = añolectivo,@curso = curso,@organizacion = organizacion from inserted
		INSERT INTO tb_Inscripciones (Organizacion,AnioLectivo,Alumno,Carrera,Curso,Escolarizado,Resuelta)
		VALUES (@organizacion,@AñoLectivo,@alumno,5,@Curso,'N','N')

	end
*/

if @Alumno is null and @organizacion is not null and @añolectivo is not null and @curso is not null begin
	select @alumno = idalumno,@añolectivo = añolectivo,@curso = curso,@organizacion = organizacion from inserted
	INSERT INTO tb_Inscripciones (Organizacion,AnioLectivo,Alumno,Carrera,Curso,Escolarizado,Resuelta)
	VALUES (@organizacion,@AñoLectivo,@alumno,5,@Curso,'S','S')
		
	if exists (Select * from tb_Inscripciones where Alumno = @alumno and organizacion = @organizacion and aniolectivo = @añolectivo) begin
				UPDATE tb_Inscripciones
				SET Alumno = @alumno,AnioLectivo = @AñoLectivo,Organizacion = @organizacion,Curso = @Curso,Escolarizado = 'S',Resuelta = 'S'
				where Alumno = @alumno and AnioLectivo = @AñoLectivo and Organizacion = @organizacion		


		if not exists (Select * from tb_AlumnoDivision where Alumno = @alumno and @AñoLectivo = @AñoLectivo and Division = @seccion) begin
			INSERT INTO tb_alumnodivision(Alumno,Division,AnioLectivo,Desde,Hasta,PaseDeOrigen,InscripcionDeOrigen,Baja,CausaBaja,cargo,hora)
			VALUES (@alumno,@curso,@AñoLectivo,@Fecha,null,null,@InscripcionDeOrigen,'N',null,system_user,getdate())
		end
 	End
End


if @Alumno is null and @organizacion is not null and @añolectivo is not null and @curso is null begin
	select @alumno = idalumno,@añolectivo = añolectivo,@curso = curso,@organizacion = organizacion from inserted
	INSERT INTO tb_Inscripciones (Organizacion,AnioLectivo,Alumno,Carrera,Curso,Escolarizado,Resuelta)
	VALUES (@organizacion,@AñoLectivo,@alumno,5,@Curso,'S','S')
		
End
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updatetb_Alumnos] ON [dbo].[tb_Alumnos]
 FOR UPDATE AS 
SET NOCOUNT ON
 IF (update(idAlumno)) begin 
	 SELECT * FROM inserted 
	 IF @@ROWCOUNT = 1 begin 
		 UPDATE tb_Cursadas
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_Cursadas.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_BecasYPrestamos
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_BecasYPrestamos.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_Inscripciones
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_Inscripciones.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_FichaConducta
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_FichaConducta.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_FichaPsicoPedagogica
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_FichaPsicoPedagogica.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_FichaSanitaria
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_FichaSanitaria.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_FichaSocioEconomica
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_FichaSocioEconomica.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_Familia
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_Familia.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_CarrerasAlumnos
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_CarrerasAlumnos.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_Solicitudes
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_Solicitudes.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_Inasistencias
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_Inasistencias.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_AlumnoDivision
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_AlumnoDivision.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_Examenes
		 SET Descripcion = (SELECT idAlumno FROM inserted)
		 WHERE tb_Examenes.Descripcion = (SELECT idAlumno FROM deleted)
		 UPDATE tb_Pases
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_Pases.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_AsignacionServiciosExtraordinarios
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_AsignacionServiciosExtraordinarios.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_Entrevistas
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_Entrevistas.Alumno = (SELECT idAlumno FROM deleted)
		 UPDATE tb_Aportes
		 SET Alumno = (SELECT idAlumno FROM inserted)
		 WHERE tb_Aportes.Alumno = (SELECT idAlumno FROM deleted)
	end  ELSE begin 
		 ROLLBACK TRANSACTION 
 end 
 end
GO
ALTER TABLE [dbo].[tb_Alumnos] ADD CONSTRAINT [PK_tb_Alumnos] PRIMARY KEY CLUSTERED  ([idAlumno]) WITH (FILLFACTOR=98) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_Alumnos] FOREIGN KEY ([Alumno]) REFERENCES [dbo].[tb_Alumnos] ([idAlumno]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_Divisionestemp] FOREIGN KEY ([Curso]) REFERENCES [dbo].[tb_Divisionestemp] ([idDivision]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_EstadosCiviles] FOREIGN KEY ([EstadoCivil]) REFERENCES [dbo].[tb_EstadosCiviles] ([idEstadoCivil])
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_idiomas] FOREIGN KEY ([LenguaFamilia]) REFERENCES [dbo].[tb_idiomas] ([ididioma])
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_Localidades] FOREIGN KEY ([Localidad]) REFERENCES [dbo].[tb_Localidades] ([idLocalidad])
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_Localidades1] FOREIGN KEY ([LugarDeNacimiento]) REFERENCES [dbo].[tb_Localidades] ([idLocalidad])
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_Nacionalidad] FOREIGN KEY ([Nacionalidad]) REFERENCES [dbo].[tb_Nacionalidad] ([idNacionalidad])
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_Organizaciones] FOREIGN KEY ([Organizacion]) REFERENCES [dbo].[tb_Organizaciones] ([idOrganizaciones]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_Seguros] FOREIGN KEY ([Seguro]) REFERENCES [dbo].[tb_Seguros] ([idSeguro])
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_Sexo] FOREIGN KEY ([Sexo]) REFERENCES [dbo].[tb_Sexo] ([idSexo])
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_TiposDeDocumento] FOREIGN KEY ([Tipo]) REFERENCES [dbo].[tb_TiposDeDocumento] ([idTipoDocumento])
GO
ALTER TABLE [dbo].[tb_Alumnos] WITH NOCHECK ADD CONSTRAINT [FK_tb_Alumnos_tb_TiposDeDomicilios] FOREIGN KEY ([TipoDomicilio]) REFERENCES [dbo].[tb_TiposDeDomicilios] ([idTipoDomicilio])
GO
GRANT SELECT ON  [dbo].[tb_Alumnos] TO [Coordinaciones]
GRANT INSERT ON  [dbo].[tb_Alumnos] TO [Coordinaciones]
GRANT UPDATE ON  [dbo].[tb_Alumnos] TO [Coordinaciones]
GO
